using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SmartLink.Domain.Entity;
using SmartLink.IRepository;
using SmartLink.Repository.Base;
using Dapper;
using SmartLink.Domain.ExpandEntity;
using SmartLink.EnumLibrary;
using System.Data.Common;
using SmartLink.Infrastructure;
using SmartLink.Domain.ExpandEntity.Base;
using SmartLink.Code;

namespace SmartLink.Repository
{
    public class BIZ_OrganizationDeptRepo : RepositoryBase<BIZ_OrganizationDepartment>, IBIZ_OrganizationDeptRepo
    {
        public IList<DepartmentExpand> GetOrganizationDeptList(int oid, string keyValue, Pagination pagination)
        {
            var selectSql = new StringBuilder(@"SELECT od.*, u.NickName CreatorName FROM BIZ_OrganizationDepartment od
                                                    LEFT JOIN SYS_User u ON od.Creator=u.ID");
            var whereSql = new StringBuilder($" WHERE od.IsDel=0");
            if (oid > -1)
            {
                whereSql.Append("  AND od.OID =@OID");
            }
            if (!string.IsNullOrEmpty(keyValue))
            {
                whereSql.Append(" AND (od.No LIKE @KeyValue OR od.DepartmentName LIKE @KeyValue OR od.DepartmentShorterName LIKE @KeyValue OR od.Phone LIKE @KeyValue)");
            }

            var dataFilter = new DataAuthorizeFilter(ModuleCodeEnum.DepartmentManage);
            var mainSql = dataFilter.WrapSql(selectSql.Append(whereSql).ToString());

            var param = new { KeyValue = $"%{keyValue}%", OID = oid };
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                if (pagination.IsSearchTotal)
                {
                    var countSql = $"SELECT COUNT(*) FROM ({mainSql}) a";
                    pagination.TotalCount = conn.ExecuteScalar<int>(countSql, param);
                }
                return conn.Query<DepartmentExpand>(mainSql + " ORDER BY CreateTime DESC " + pagination.ToLimitString(), param).ToList();
            }
        }

        public BIZ_OrganizationDepartment GetMaxSortDeptByParentId(int? orgDeptPid)
        {
            BIZ_OrganizationDepartment dept;
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                string whereStr = $"where isdel={IsDelEnum.未删除.GetHashCode()}";
                if (orgDeptPid.HasValue && orgDeptPid.Value != ConstData.SelectAllOptionValue)
                    whereStr += $" AND ParentID={orgDeptPid.Value}";
                else
                    whereStr += " AND ParentID IS NULL";
                var sql = string.Format(" select  * from BIZ_OrganizationDepartment {0} order by sort desc limit 1", whereStr);
                BIZ_OrganizationDepartment originalDept = conn.QueryFirstOrDefault<BIZ_OrganizationDepartment>(sql);
                dept = originalDept;
                conn.Close();
            }
            return dept;
        }

        public bool Delete(int orgDeptId)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var rowsAffected = conn.Execute("UPDATE BIZ_OrganizationDepartment SET IsDel=1 WHERE ID=@ID", new { ID = orgDeptId });
                return rowsAffected > 0;
            }
        }

        public bool DeleteByOrgId(int orgId)
        {
            throw new NotImplementedException();
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                string whereStr = $" where oid = '{orgId}'";
                var sql = string.Format(" delete from BIZ_OrganizationDepartment {0} ", whereStr);
                int count = conn.Execute(sql);
                conn.Close();
            }
            return true;
        }

        /// <summary>
        /// 获取全部部门
        /// </summary>
        /// <returns></returns>
        public List<int> GetAllChildDeptIds()
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql =
                    "select bod.ID from biz_organization bo inner join BIZ_OrganizationDepartment bod on bod.OID=bo.ID where bo.isdel=0 and bod.isdel=0;";

                var list = conn.Query<int>(sql).ToList();
                conn.Close();
                return list;
            }
        }



        public string GetAllChildDeptsStr(int orgDeptId)
        {
            IList<int> depts = GetSelfAndDescendantsIdList(orgDeptId);
            if (depts.Count == 0)
                return "";
            else
                return string.Join(",", depts);
        }


        /// <summary>
        /// 根据部门IDS获取部门列表
        /// </summary>
        /// <param name="deptIds"></param>
        /// <returns></returns>
        public IList<BIZ_OrganizationDepartment> GetAllChildDepts(IList<int> deptIds)
        {
            IList<BIZ_OrganizationDepartment> depts = new List<BIZ_OrganizationDepartment>();
            if (deptIds.Count == 0)
                return depts;

            string idsStr = string.Join(",", deptIds);
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                string whereStr = string.Format(" where isdel={0} and id in ({1})", IsDelEnum.未删除.GetHashCode(), idsStr);
                var sql = string.Format(" select * from BIZ_OrganizationDepartment {0} ", whereStr);
                depts = conn.Query<BIZ_OrganizationDepartment>(sql).ToList();
                conn.Close();
            }
            return depts;
        }

        /// <summary>
        /// 上面两个方法的合体 根据部门id获取 部门下的部门entity集合
        /// </summary>
        /// <param name="orgDeptId"></param>
        /// <returns></returns>
        public IList<BIZ_OrganizationDepartment> GetAllChildDeptsByDeptId(int orgDeptId)
        {
            IList<int> deptIds = GetSelfAndDescendantsIdList(orgDeptId);
            IList<BIZ_OrganizationDepartment> depts = GetAllChildDepts(deptIds);
            return depts;
        }


        public BIZ_OrganizationDepartment GetSingleOrganizationDepartment(int orgDeptId)
        {
            BIZ_OrganizationDepartment result;
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = string.Format(" select *  from BIZ_OrganizationDepartment where  id={0} and isdel={1}", orgDeptId, IsDelEnum.未删除.GetHashCode());
                result = conn.QueryFirstOrDefault<BIZ_OrganizationDepartment>(sql);
                conn.Close();
            }
            return result;
        }

        /// <summary>
        /// 获取所有门店
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public IList<BIZ_OrganizationDepartment> GetAllStoreByOrgId(int orgId)
        {
            IList<BIZ_OrganizationDepartment> results = new List<BIZ_OrganizationDepartment>();
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = string.Format($" select *  from BIZ_OrganizationDepartment where oid=@oid and isdel={IsDelEnum.未删除.GetHashCode()} and sign=1");
                results = conn.Query<BIZ_OrganizationDepartment>(sql, new { oid = orgId }).AsList();
                conn.Close();
            }
            return results;
        }


        /// <summary>
        /// 根据部门名称和公司ID获取部门信息
        /// </summary>
        /// <param name="departmentName"></param>
        /// <param name="oid"></param>
        /// <returns></returns>
        public BIZ_OrganizationDepartment GetByNameAndOid(string departmentName, int oid)
        {
            BIZ_OrganizationDepartment result;
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = string.Format($" select *  from BIZ_OrganizationDepartment where isdel={IsDelEnum.未删除.GetHashCode()} and DepartmentName=@DepartmentName and OID=@OID limit 1;");
                result = conn.QueryFirstOrDefault<BIZ_OrganizationDepartment>(sql, new { DepartmentName = departmentName, OID = oid });
                conn.Close();
            }
            return result;
        }

        public bool CheckExistByDeptName(int oid, string DeptName)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = string.Format($@"SELECT count(*) from BIZ_OrganizationDepartment where isdel={IsDelEnum.未删除.GetHashCode()} and (DepartmentName='{DeptName}' or DepartmentShorterName='{DeptName}') and oid={oid}");
                int count = Convert.ToInt32(conn.ExecuteScalar(sql).ToString());
                CloseConn(conn);
                return count > 0 ? true : false;
            }
        }

        #region 树查询

        /// <summary>
        /// 组织机构表树结构关系表
        /// </summary>
        private IEnumerable<SelfRelationRow> _selfTreeStructure
        {
            get
            {
                return base.SelfTreeStructure(DapperHelper.GetTableName(typeof(BIZ_OrganizationDepartment)));
            }
        }
        private IEnumerable<BIZ_OrganizationDepartment> GetDepartments(string idConcatStr)
        {
            var sql = "SELECT * FROM BIZ_OrganizationDepartment WHERE FIND_IN_SET(ID, @IdStr) AND IsDel=0";
            if (!string.IsNullOrEmpty(idConcatStr))
            {
                using (var conn = CreateConn())
                {
                    return conn.Query<BIZ_OrganizationDepartment>(sql, new { IdStr = idConcatStr });
                }
            }
            return null;
        }
        /// <summary>
        /// 获取祖先节点集合
        /// </summary>
        /// <param name="topOdid"></param>
        /// <returns></returns>
        public IEnumerable<BIZ_OrganizationDepartment> GetAncestors(int topOdid, bool withSelf = false)
        {
            var ancestorsPath = _selfTreeStructure.Where(a => a.ID == topOdid).Select(a => a.Path).SingleOrDefault();
            if (withSelf)
                ancestorsPath = string.Join(",", ancestorsPath, topOdid);
            return GetDepartments(ancestorsPath);
        }
        /// <summary>
        /// 获取后代节点结婚
        /// </summary>
        /// <param name="topOdid"></param>
        /// <returns></returns>
        public IEnumerable<BIZ_OrganizationDepartment> GetDescendants(int topOdid, bool withSelf = false)
        {
            var descendantsIds = _selfTreeStructure.Where(a => !string.IsNullOrEmpty(a.Path) && SelfRelationRow.ParsePath(a.Path).Contains(topOdid)).Select(a => a.ID);
            var descendantsPath = string.Join(",", descendantsIds);
            if (withSelf)
                descendantsPath = string.Join(",", topOdid, descendantsPath);
            return GetDepartments(descendantsPath);
        }

        public IList<int> GetSelfAndDescendantsIdList(int deptId)
        {
            var descendantsIdList = _selfTreeStructure.Where(a => !string.IsNullOrEmpty(a.Path) && SelfRelationRow.ParsePath(a.Path).Contains(deptId)).Select(a => a.ID).ToList();
            IList<int> ids = new List<int> { deptId };
            ids = ids.Union(descendantsIdList).ToList();
            return ids;
        }

        #endregion

        /// <summary>
        /// 获取一个公司下所有的部门
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public IEnumerable<BIZ_OrganizationDepartment> GetOrgDeptList(int orgId)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = string.Format("SELECT * FROM BIZ_OrganizationDepartment WHERE oid=@oid AND IsDel=0");
                return conn.Query<BIZ_OrganizationDepartment>(sql, new { oid = orgId });
            }
        }

        public IList<int> GetAllChildDeptIds(int orgDeptId)
        {
            throw new NotImplementedException();
        }

        public bool ExistDescendantDepts(int deptId)
        {
            var descendants = GetDescendants(deptId);
            return descendants.Count() > 0;
        }
    }
}
